/*
 * $Id: db.c 1036 2008-12-16 06:59:09Z jasta00 $
 *
 * Copyright (C) 2008 Josh Guilfoyle <jasta@devtcg.org>
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the
 * Free Software Foundation; either version 2, or (at your option) any
 * later version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * General Public License for more details.
 */

#include "main.h"

#include "utils.h"

#include "db.h"

/*****************************************************************************/

sqlite3 *db = NULL;
#define DB_VERSION (30)

static char *db_path = NULL;

static char *create_sync_events =
"CREATE TABLE sync_events ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"last INTEGER NOT NULL, "          /* Client; Previous "next" sync anchor */
	"timestamp INTEGER NOT NULL, "     /* Server; Previous "next" sync anchor */
	"src TEXT UNIQUE NOT NULL"         /* Unique client id. */
");";
static char *drop_sync_events =
"DROP TABLE IF EXISTS sync_events;";

static char *create_artists =
"CREATE TABLE artists ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"mbid INTEGER, "
	"name TEXT NOT NULL, "
	"name_match TEXT UNIQUE NOT NULL, "
	"photo_thumb BLOB, "
	"photo_big BLOB, "
	"genre TEXT, "
	"discovery_date DATETIME, "
	"pending_delete INTEGER DEFAULT 0 NOT NULL "
");"
"CREATE INDEX artist_genre ON artists (genre);"
"CREATE INDEX artist_discovery ON artists (discovery_date);"
"CREATE INDEX artist_pending_delete ON artists (pending_delete);"
"CREATE TRIGGER artist_delete_t BEFORE UPDATE OF pending_delete ON artists "
	"FOR EACH ROW WHEN NEW.pending_delete = 1 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 3, 'artist', OLD._id);"
	"END;"
"CREATE TRIGGER artist_undelete_t BEFORE UPDATE OF pending_delete ON artists "
	"FOR EACH ROW WHEN NEW.pending_delete = 0 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'artist', OLD._id);"
	"END;"
"CREATE TRIGGER artist_insert_t AFTER INSERT ON artists "
	"FOR EACH ROW BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'artist', NEW._id);"
	"END;";

static char *drop_artists =
"DROP TABLE IF EXISTS artists;";

static char *create_albums =
"CREATE TABLE albums ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"mbid INTEGER, "
	"name TEXT NOT NULL, "
	"name_match TEXT NOT NULL, "
	"artist_id INTEGER, "
	"artwork_thumb BLOB, "
	"artwork_big BLOB, "
	"release_date DATE, "
	"discovery_date DATETIME, "
	"pending_delete INTEGER DEFAULT 0 NOT NULL "
");"
"CREATE UNIQUE INDEX album_name ON albums (artist_id, name_match);"
"CREATE INDEX album_artist_id ON albums (artist_id);"
"CREATE INDEX album_release_date ON albums (release_date);"
"CREATE INDEX album_discovery ON albums (discovery_date);"
"CREATE INDEX album_pending_delete ON albums (pending_delete);"
"CREATE TRIGGER album_delete_t BEFORE UPDATE OF pending_delete ON albums "
	"FOR EACH ROW WHEN NEW.pending_delete = 1 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 3, 'album', OLD._id);"
	"END;"
"CREATE TRIGGER album_undelete_t BEFORE UPDATE OF pending_delete ON albums "
	"FOR EACH ROW WHEN NEW.pending_delete = 0 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'album', OLD._id);"
	"END;"
"CREATE TRIGGER album_insert_t AFTER INSERT ON albums "
	"FOR EACH ROW BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'album', NEW._id);"
	"END;";

static char *drop_albums =
"DROP TABLE IF EXISTS albums;";

static char *create_songs =
"CREATE TABLE songs ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"mbid INTEGER, "
	"filename TEXT UNIQUE NOT NULL, "
	"mimetype TEXT NOT NULL, "
	"mtime DATETIME NOT NULL, "
	"bitrate INTEGER NOT NULL, "
	"tick INTEGER, "
	"size INTEGER NOT NULL, "
	"title TEXT NOT NULL, "
	"artist_id INTEGER, "
	"album_id INTEGER, "
	"length INTEGER, "
	"track INTEGER, "
	"genre TEXT, "
	"discovery_date DATETIME, "
	"pending_delete INTEGER DEFAULT 0 NOT NULL "
");"
"CREATE INDEX song_filename ON songs (filename);"
"CREATE INDEX song_artist_id ON songs (artist_id);"
"CREATE INDEX song_album_id ON songs (album_id);"
"CREATE INDEX song_genre ON songs (genre);"
"CREATE INDEX song_discovery ON songs (discovery_date);"
"CREATE INDEX song_pending_delete ON songs (pending_delete);"
"CREATE TRIGGER song_delete_t BEFORE UPDATE OF pending_delete ON songs "
	"FOR EACH ROW WHEN NEW.pending_delete = 1 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 3, 'song', OLD._id);"
	"END;"
"CREATE TRIGGER song_undelete_t BEFORE UPDATE OF pending_delete ON songs "
	"FOR EACH ROW WHEN NEW.pending_delete = 0 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'song', OLD._id);"
	"END;"
"CREATE TRIGGER song_insert_t AFTER INSERT ON songs "
	"FOR EACH ROW BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'song', NEW._id);"
	"END;";

static char *drop_songs =
"DROP TABLE IF EXISTS songs;";

static char *create_playlists =
"CREATE TABLE playlists ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"name TEXT NOT NULL, "
	"filename TEXT UNIQUE NOT NULL, "
	"created_date DATETIME NOT NULL, "
	"tick INTEGER, "
	"pending_delete INTEGER DEFAULT 0 NOT NULL "
");"
"CREATE INDEX playlists_filename ON playlists (filename);"
"CREATE INDEX playlists_created_date ON playlists (created_date);"
"CREATE TRIGGER playlist_delete_t BEFORE UPDATE OF pending_delete ON playlists "
	"FOR EACH ROW WHEN NEW.pending_delete = 1 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 3, 'playlist', OLD._id);"
	"END;"
"CREATE TRIGGER playlist_undelete_t BEFORE UPDATE OF pending_delete ON playlists "
	"FOR EACH ROW WHEN NEW.pending_delete = 0 BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'playlist', OLD._id);"
	"END;"
"CREATE TRIGGER playlist_insert_t AFTER INSERT ON playlists "
	"FOR EACH ROW BEGIN "
		"INSERT INTO item_changelog (timestamp, event, domain, item_id) VALUES (strftime('%s', 'now'), 1, 'playlist', NEW._id);"
	"END;";

static char *drop_playlists =
"DROP TABLE IF EXISTS playlists;";

static char *create_playlist_songs =
"CREATE TABLE playlist_songs ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"playlist_id INTEGER NOT NULL, "
	"song_id INTEGER NOT NULL, "
	"position INTEGER NOT NULL, "
	"pending_delete INTEGER DEFAULT 0 NOT NULL "
");"
"CREATE INDEX playlist_songs_song_id ON playlist_songs (song_id);";

static char *drop_playlist_songs =
"DROP TABLE IF EXISTS playlist_songs;";

static char *create_item_changelog =
"CREATE TABLE item_changelog ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"timestamp DATETIME NOT NULL, "
	"event INTEGER NOT NULL, "
	"domain TEXT NOT NULL, "
	"item_id INTEGER NOT NULL "
");"
"CREATE INDEX changelog_timestamp ON item_changelog (timestamp);"
"CREATE INDEX changelog_domain ON item_changelog (domain);";

static char *drop_item_changelog =
"DROP TABLE IF EXISTS item_changelog;";

static char *create_item_map =
"CREATE TABLE item_map ("
	"_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	"src TEXT NOT NULL, "
	"domain TEXT NOT NULL, "
	"guid INTEGER NOT NULL, "
	"luid INTEGER NOT NULL "
");"
"CREATE INDEX map_src ON item_map (src);"
"CREATE INDEX map_domain ON item_map (domain);"
"CREATE UNIQUE INDEX map_item ON item_map (src, domain, guid);"
"CREATE UNIQUE INDEX map_item2 ON item_map (src, domain, luid);";

static char *drop_item_map =
"DROP TABLE IF EXISTS item_map;";

/*****************************************************************************/

static void db_mustdo(const char *sql)
{
	char *err;
	int rc;

	if ((rc = sqlite3_exec(db, sql, NULL, NULL, &err)) != SQLITE_OK)
		g_error("Unable to execute SQL statement: %s\n", err);
}

static void db_do(const char *sql)
{
	sqlite3_exec(db, sql, NULL, NULL, NULL);
}

static gboolean db_create(void)
{
	char *err;
	char *sql;
	int rc;

	db_mustdo("BEGIN TRANSACTION;");
	db_mustdo("CREATE TABLE parameters (version INT NOT NULL);");

	sql = g_strdup_printf("INSERT INTO parameters (version) VALUES (%d);",
	  DB_VERSION);
	db_mustdo(sql);
	g_free(sql);

	db_mustdo(create_sync_events);
	db_mustdo(create_artists);
	db_mustdo(create_albums);
	db_mustdo(create_songs);
	db_mustdo(create_playlists);
	db_mustdo(create_playlist_songs);
	db_mustdo(create_item_changelog);
	db_mustdo(create_item_map);
	db_mustdo("COMMIT;");

	return TRUE;
}

static void db_drop(void)
{
	sqlite3_exec(db, "DROP TABLE IF EXISTS parameters", NULL, NULL, NULL);

	db_do(drop_sync_events);
	db_do(drop_artists);
	db_do(drop_albums);
	db_do(drop_songs);
	db_do(drop_playlists);
	db_do(drop_playlist_songs);
	db_do(drop_item_changelog);
	db_do(drop_item_map);
}

static gboolean db_get_version(unsigned int *version)
{
	sqlite3_stmt *stmt;
	int rc;

	assert(version != NULL);

	rc = sqlite3_prepare(db, "SELECT version FROM parameters;",
	  -1, &stmt, NULL);

	if (rc != SQLITE_OK)
	{
		sqlite3_finalize(stmt);
		return FALSE;
	}

	rc = sqlite3_step(stmt);

	if (rc != SQLITE_ROW)
	{
		sqlite3_finalize(stmt);
		return FALSE;
	}

	*version = (unsigned int)sqlite3_column_int(stmt, 0);

	sqlite3_finalize(stmt);
	return TRUE;
}

static gboolean db_upgrade(unsigned int oldVersion)
{
	g_warning("Dropping database to upgrade...\n");

	db_drop();

	if (db_create() == FALSE)
		return FALSE;
}

static const char *db_get_path(void)
{
	if (db_path != NULL)
		return db_path;

	db_path = g_build_filename(five_home_base(), "server.db", NULL);

	return db_path;
}

gboolean db_open_again(sqlite3 **dbc)
{
	unsigned int ver;
	int rc;

	if ((rc = sqlite3_open(db_get_path(), dbc)) != SQLITE_OK)
	{
		g_critical("Can't initialize meta database %s: %s\n",
		  db_get_path(), sqlite3_errmsg(*dbc));
		goto error;
	}

	if (db_get_version(&ver) == FALSE)
		goto error;

	if (ver < DB_VERSION)
	{
		g_critical("Database version too old, expected version %d, got version %d", DB_VERSION, (int)ver);
		goto error;
	}

	return TRUE;

error:
	sqlite3_close(*dbc);

	return FALSE;
}

void db_close_again(sqlite3 *dbc)
{
	sqlite3_close(dbc);
}

gboolean db_init(gboolean use_existing)
{
	unsigned int ver;
	int rc;

	if (use_existing == FALSE)
		g_unlink(db_get_path());

	if ((rc = sqlite3_open(db_get_path(), &db)) != SQLITE_OK)
	{
		g_critical("Can't initialize meta database %s: %s\n",
		  db_get_path(), sqlite3_errmsg(db));
		goto error;
	}

	if (db_get_version(&ver) == FALSE)
	{
		g_message("Creating database %s", db_get_path());

		if (db_create() == FALSE)
			goto error;

		ver = DB_VERSION;
	}

	printf("ver = %d\n", ver);

	if (ver < DB_VERSION)
	{
		if (db_upgrade(ver) == FALSE)
			goto error;
	}

	return TRUE;

error:
	sqlite3_close(db);

	return FALSE;
}

void db_finish(void)
{
	if (db_path != NULL)
		g_free(db_path);

	sqlite3_close(db);
}
